﻿using Mono.Data.Sqlite;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using UnityEngine;

public class SQLManager : MonoBehaviour
{
    public static SQLManager s_Instance;
    /// <summary>
    /// 数据库连接对象
    /// </summary>
    private SqliteConnection connection;
    /// <summary>
    /// 数据库命令
    /// </summary>
    private SqliteCommand command;
    /// <summary>
    /// 数据读取定义
    /// </summary>
    private SqliteDataReader reader;
    /// <summary>
    /// 本地数据库名字
    /// </summary>
    public string sqlName;


    private void Awake() {
        s_Instance = this;
        GameObject.DontDestroyOnLoad(gameObject);
    }


    public void Start() {
        this.CreateSQL();
        this.OpenSQLaAndConnect();
    }
    //创建数据库文件
    public void CreateSQL() {
        if (!File.Exists(Application.streamingAssetsPath + "/" + sqlName)) {
            connection = new SqliteConnection("data source=" + Application.streamingAssetsPath + "/" + sqlName);
            connection.Open();
            CreateSQLTable(
                "答题表",
                "CREATE TABLE ANSWER_TABLE(" +
                "PAPER_ID       INT," +
                "USER_ID        TEXT," +
                "OPT_CHOSED     TEXT," +
                "FILLBLANK      TEXT," +
                "SCORE          INT," +
                "TYPE           INT," +
                "TIME           TEXT," +
                "QID            INT)",
                null,
                null
            );
            CreateSQLTable(
                "权限表",
                "CREATE TABLE USER_TABLE(" +
                "ID             TEXT primary key," +
                "Auth           INT)",
                null,
                null
            );
            CreateSQLTable(
                "题库",
                "CREATE TABLE QUESTION_TABLE(" +
                "ID             INT primary key ," +
                "Type           INT," +
                "Stem           TEXT," +
                "Pic_Path       TEXT)",
                null,
                null
            );
            CreateSQLTable(
                "选项库",
                "CREATE TABLE OPTION_TABLE(" +
                "ID             INTEGER primary key AUTOINCREMENT," +
                "QID            INT," +
                "Stem           TEXT," +
                "IsRight        INT," +
                "Jump_ID        INT," +
                "Score          INT)",
                null,
                null
            );
            CreateSQLTable(
               "试卷库",
               "CREATE TABLE PAPER_TABLE(" +
               "ID              INTEGER primary key AUTOINCREMENT," +
               "Stem            TEXT," +
               "Editor          TEXT," +
               "Name            TEXT)",
               null,
               null
           );
            CreateSQLTable(
               "试卷题列表库",
               "CREATE TABLE PAPER_QUESTION_TABLE(" +
               "Paper_ID        INT," +
               "Question_ID     TEXT)",
               null,
               null
           );
            connection.Close();
            return;
        }

    }
    //打开数据库
    public void OpenSQLaAndConnect() {
        connection = new SqliteConnection("data source=" + Application.streamingAssetsPath + "/" + sqlName);
        connection.Open();
    }
    /// <summary>
    ///执行SQL命令,并返回一个SqliteDataReader对象
    /// <param name="queryString"></param>
    public SqliteDataReader ExecuteSQLCommand(string queryString) {
        print(queryString);
        command = connection.CreateCommand();
        command.CommandText = queryString;
        try {
            reader = command.ExecuteReader();
            return reader;
        }
        catch(Exception e) {
            Debug.LogWarning(e);
        }
        return null;
        
    }
    /// <summary>
    /// 通过调用SQL语句，在数据库中创建一个表，顶定义表中的行的名字和对应的数据类型
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="columnNames"></param>
    /// <param name="dataTypes"></param>
    public SqliteDataReader CreateSQLTable(string tableName, string commandStr = null, string[] columnNames = null, string[] dataTypes = null) {
        //string queryString = "CREATE TABLE " + tableName + "( " + columnNames[0] + " " + dataTypes[0];
        //for (int i = 1; i < columnNames.Length; i++)
        //{
        //    queryString += ", " + columnNames[i] + " " + dataTypes[i];
        //}
        //queryString += "  ) ";
        //  return ExecuteSQLCommand(queryString);

        return ExecuteSQLCommand(commandStr);
    }
    /// <summary>
    /// 关闭数据库连接,注意这一步非常重要，最好每次测试结束的时候都调用关闭数据库连接
    /// 如果不执行这一步，多次调用之后，会报错，数据库被锁定，每次打开都非常缓慢
    /// </summary>
    public void CloseSQLConnection() {
        if (command != null) {
            command.Cancel();
        }

        if (reader != null) {
            reader.Close();
        }

        if (connection != null) {
            connection.Close();

        }
        command = null;
        reader = null;
        connection = null;
        Debug.Log("已经断开数据库连接");
    }
    //当退出应用程序的时候关闭数据库连接
    private void OnApplicationQuit() {
        //当程序退出时关闭数据库连接，不然会重复打开数据卡，造成卡顿
        CloseSQLConnection();
        Debug.Log("程序退出");
    }
}
